﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using ASBCC.Database.Entity;

namespace ASBCC.Database.Service
{
    public class K2Data
    {
        public class Act
        {
            public int ID;
            public int ProcID;
            public string Name;

            public Act(int id, int procID, string name)
            {
                this.ID = id;
                this.ProcID = procID;
                this.Name = name;
            }
        }

        public class Line
        {
            public int ID;
            public int ProcID;
            public string Name;
            public int StartID;
            public int FinishID;
            public string StartActivityName;
            public string FinishActivityName;

            public Line(int id, int procID, string name, int startID, int finishID, string startActivityName, string finishActivityName)
            {
                this.ID = id;
                this.ProcID = procID;
                this.Name = name;
                this.StartID = startID;
                this.FinishID = finishID;
                this.StartActivityName = startActivityName;
                this.FinishActivityName = finishActivityName;
            }
        }
    }

    public static class K2DataService
    {
        public static int GetProcID(int procInstID)
        {
            string sql = "select ProcID from _ProcInst where ID=@ID";
            SqlParameter sqlParam = new SqlParameter("@ID", SqlDbType.Int);
            sqlParam.Value = procInstID;
            object objScalar = SqlDataHelper.ExecuteScalar(
                DatabaseManager.K2ConnectionString,
                CommandType.Text,
                sql,
                sqlParam
                );

            if (objScalar == null || objScalar == DBNull.Value) return -1;
            return Convert.ToInt32(objScalar);
        }

        public static int GetProcID(string processName)
        {
            string sql = "select ProcVerID from _Procset where FullName=@FullName";
            SqlParameter sqlParam = new SqlParameter("@FullName", SqlDbType.NVarChar, 1024);
            sqlParam.Value = processName;
            object objScalar = SqlDataHelper.ExecuteScalar(
                DatabaseManager.K2ConnectionString,
                CommandType.Text,
                sql,
                sqlParam
                );

            if (objScalar == null || objScalar == DBNull.Value) return -1;
            return Convert.ToInt32(objScalar);
        }

        public static int GetProcessInstanceStatus(int procInstId)
        {
            if (procInstId <= 0) return 6;
            string commandText = "select Status from _ProcInst where ID=@ProcInstID";
            SqlParameter sqlParam = new SqlParameter("@ProcInstID", SqlDbType.Int);
            sqlParam.Value = procInstId;
            object objStatus = SqlDataHelper.ExecuteScalar(
                DatabaseManager.K2ConnectionString,
                CommandType.Text,
                commandText,
                sqlParam
                );
            if (objStatus == null || objStatus == DBNull.Value) return 6;
            return Convert.ToInt32(objStatus);
        }

        public static List<K2Data.Act> GetAct(int procID)
        {
            string sql = "select ID,ProcID,[Name] from _act where ProcID=@ProcID";
            SqlParameter sqlParam = new SqlParameter("@ProcID", SqlDbType.Int);
            sqlParam.Value = procID;
            DataSet ds = SqlDataHelper.ExecuteDataset(
                DatabaseManager.K2ConnectionString,
                CommandType.Text,
                sql,
                sqlParam
                );

            if (ds == null || ds.Tables[0].Rows.Count == 0)
                return null;

            List<K2Data.Act> lst = new List<K2Data.Act>();
            foreach (DataRow dr in ds.Tables[0].Rows)
            {
                int id;
                if (!int.TryParse(dr["ID"].ToString(), out id))
                    continue;
                K2Data.Act act = new K2Data.Act(id, procID, dr["Name"].ToString());
                lst.Add(act);
            }
            if (lst.Count == 0)
                return null;

            return lst;

        }

        /// <summary>
        /// 根据流程ID获得第一个节点的名称(实际为第二个,因为要排除Start节点)
        /// </summary>
        /// <param name="procID"></param>
        /// <returns></returns>
        public static string GetFirstActivityName(int procID)
        {
            string sql =
@"select [Name] from _act where ID=
(
	select FinishID from _line
	where procid=@ProcID and StartID not in
		(
			select FinishID from _line where procid=@ProcID
		)
)";
            SqlParameter sqlParam = new SqlParameter("@ProcID", SqlDbType.Int);
            sqlParam.Value = procID;
            DataSet ds = SqlDataHelper.ExecuteDataset(
                DatabaseManager.K2ConnectionString,
                CommandType.Text,
                sql,
                sqlParam
                );
            if (ds == null || ds.Tables[0].Rows.Count == 0)
                return string.Empty;
            return ds.Tables[0].Rows[0][0].ToString();
        }

        public static Flow_ActionRecordTableEntity GetLastActionRecord(int procInstID)
        {
            string sql = "select top 1 * from Flow_ActionRecord where ProcInstID=@ProcInstID order by ActionDate desc";
            SqlParameter sqlParam = new SqlParameter("@ProcInstID", SqlDbType.Int);
            sqlParam.Value = procInstID;
            DataSet ds = SqlDataHelper.ExecuteDataset(
                DatabaseManager.ConnectionString,
                CommandType.Text,
                sql,
                sqlParam
                );
            if (ds == null || ds.Tables.Count == 0 || ds.Tables[0].Rows.Count == 0) return null;
            Flow_ActionRecordTableEntitySet set = new Flow_ActionRecordTableEntitySet();
            set.Merge(ds);
            return set[0];
        }

        public static DataTable GetActivityLine(int procID)
        {
            string sql = @"select l.ID,l.ProcID,l.Name,l.StartID,s.Name as StartActivityName,l.FinishID,f.Name as FinishActivityName,cast('' as nvarchar(20)) as LineRule
from dbo._Line as l 
left join dbo._Act as s on l.StartID=s.ID and l.ProcID=s.ProcID 
left join dbo._Act as f on l.FinishID=f.ID and l.ProcID=f.ProcID 
where l.ProcID=@ProcID";

            SqlParameter sqlParam = new SqlParameter("@ProcID", SqlDbType.Int);
            sqlParam.Value = procID;
            DataSet ds = SqlDataHelper.ExecuteDataset(
                DatabaseManager.K2ConnectionString,
                CommandType.Text,
                sql,
                sqlParam
                );

            if (ds == null || ds.Tables.Count == 0) return null;
            return ds.Tables[0];
        }

        public static DataTable GetProcessStamp(int procInstId)
        {
            string commandText = "select *, cast('' as nvarchar(20)) as StampType from Flow_ProcessStamp where Status=0 and ProcInstID=@ProcInstID order by CreatedDate";
            SqlParameter sqlParam = new SqlParameter("@ProcInstId", SqlDbType.Int);
            sqlParam.Value = procInstId;
            DataSet ds = SqlDataHelper.ExecuteDataset(
               DatabaseManager.ConnectionString,
               CommandType.Text,
               commandText,
               sqlParam
               );
            if (ds == null || ds.Tables.Count == 0) return null;
            return ds.Tables[0];
        }

        public static DataTable GetProcessStampEZVisit(int procInstId)
        {
            string commandText = "select *, cast('' as nvarchar(20)) as StampType from Flow_ProcessStamp where Status=0 and ProcInstID=@ProcInstID and ActivityName not like '%Post%' order by CreatedDate";
            SqlParameter sqlParam = new SqlParameter("@ProcInstId", SqlDbType.Int);
            sqlParam.Value = procInstId;
            DataSet ds = SqlDataHelper.ExecuteDataset(
               DatabaseManager.ConnectionString,
               CommandType.Text,
               commandText,
               sqlParam
               );
            if (ds == null || ds.Tables.Count == 0) return null;
            return ds.Tables[0];
        }

        public static DataTable GetProcessStampPostEZVisit(int procInstId)
        {
            string commandText = "select *, cast('' as nvarchar(20)) as StampType from Flow_ProcessStamp where Status=0 and ProcInstID=@ProcInstID and ActivityName like '%Post%' order by CreatedDate";
            SqlParameter sqlParam = new SqlParameter("@ProcInstId", SqlDbType.Int);
            sqlParam.Value = procInstId;
            DataSet ds = SqlDataHelper.ExecuteDataset(
               DatabaseManager.ConnectionString,
               CommandType.Text,
               commandText,
               sqlParam
               );
            if (ds == null || ds.Tables.Count == 0) return null;
            return ds.Tables[0];
        }

        public static int DisableProcessStamp(int procInstId)
        {
            string commandText = "update Flow_ProcessStamp set Status=1 where ProcInstID=@ProcInstID ";
            SqlParameter sqlParam = new SqlParameter("@ProcInstId", SqlDbType.Int);
            sqlParam.Value = procInstId;
            return SqlDataHelper.ExecuteNonQuery(
               DatabaseManager.ConnectionString,
               CommandType.Text,
               commandText,
               sqlParam
               );
        }

        public static int ClearDisabledProcessStamp(int procInstId)
        {
            string commandText = "delete from Flow_ProcessStamp where Status=1 and ProcInstID=@ProcInstID ";
            SqlParameter sqlParam = new SqlParameter("@ProcInstId", SqlDbType.Int);
            sqlParam.Value = procInstId;
            return SqlDataHelper.ExecuteNonQuery(
               DatabaseManager.ConnectionString,
               CommandType.Text,
               commandText,
               sqlParam
               );
        }

        public static int DisableProcessStampByActionRecordId(Guid actionRecordId)
        {
            string commandText = "update Flow_ProcessStamp set Status=1 where ActionRecordID=@ActionRecordID ";
            SqlParameter sqlParam = new SqlParameter("@ActionRecordID", SqlDbType.UniqueIdentifier);
            sqlParam.Value = actionRecordId;
            return SqlDataHelper.ExecuteNonQuery(
               DatabaseManager.ConnectionString,
               CommandType.Text,
               commandText,
               sqlParam
               );
        }

        public static int DisableProcessComment(int procInstId)
        {
            string commandText = "update Flow_ProcessComment set Status=1 where ProcInstID=@ProcInstID ";
            SqlParameter sqlParam = new SqlParameter("@ProcInstID", SqlDbType.Int);
            sqlParam.Value = procInstId;
            return SqlDataHelper.ExecuteNonQuery(
               DatabaseManager.ConnectionString,
               CommandType.Text,
               commandText,
               sqlParam
               );
        }

        public static int ClearDisabledProcessComment(int procInstId)
        {
            string commandText = "delete from Flow_ProcessComment where Status=1 and ProcInstID=@ProcInstID ";
            SqlParameter sqlParam = new SqlParameter("@ProcInstID", SqlDbType.Int);
            sqlParam.Value = procInstId;
            return SqlDataHelper.ExecuteNonQuery(
               DatabaseManager.ConnectionString,
               CommandType.Text,
               commandText,
               sqlParam
               );
        }

        public static int DisableProcessCommentByActionRecordId(Guid actionRecordId)
        {
            string commandText = "update Flow_ProcessComment set Status=1 where ActionRecordID=@ActionRecordID ";
            SqlParameter sqlParam = new SqlParameter("@ActionRecordID", SqlDbType.UniqueIdentifier);
            sqlParam.Value = actionRecordId;
            return SqlDataHelper.ExecuteNonQuery(
               DatabaseManager.ConnectionString,
               CommandType.Text,
               commandText,
               sqlParam
               );
        }

        public static void MoveMyActivityListToHistroy(List<Guid> myActivityIDList)
        {
            string moveMyActivitySqlFormat = "insert into MyActivityHist select * from MyActivity where 1<>1 {0} ";
            string deleteMyActivitySqlFormat = "delete from MyActivity where 1<>1 {0} ";
            string updateMyActivityHistSqlFormat = "update MyActivityHist set ModifiedDate=getdate() where 1<>1 {0} ";
            string condition = string.Empty;
            if (myActivityIDList != null && myActivityIDList.Count > 0)
            {
                List<string> idStrList = new List<string>();
                foreach (Guid guid in myActivityIDList)
                {
                    idStrList.Add(string.Format("'{0}'", guid));
                }
                condition = string.Format(" or MyActivityID in ({0})", string.Join(",", idStrList.ToArray()));
            }

            string moveMyActivitySql = string.Format(moveMyActivitySqlFormat, condition);
            string deleteMyActivitySql = string.Format(deleteMyActivitySqlFormat, condition);
            string updateMyActivityHistSql = string.Format(updateMyActivityHistSqlFormat, condition);
            string sql = string.Format("{0} {1} {2}", moveMyActivitySql, deleteMyActivitySql, updateMyActivityHistSql);

            SqlDataHelper.ExecuteNonQuery(
                DatabaseManager.ConnectionString,
                CommandType.Text,
                sql);
        }

        public static void MoveMyActivityListToHistroy(Guid actionRecordId)
        {
            if (actionRecordId == Guid.Empty) return;
            string moveMyActivitySql = "insert into MyActivityHist select * from MyActivity where ActionRecordID=@ActionRecordID ";
            string deleteMyActivitySql = "delete from MyActivity where ActionRecordID=@ActionRecordID ";
            string updateMyActivityHistSql = "update MyActivityHist set ModifiedDate=getdate() where ActionRecordID=@ActionRecordID ";

            string sql = string.Format("{0} {1} {2}", moveMyActivitySql, deleteMyActivitySql, updateMyActivityHistSql);
            SqlParameter sqlParam = new SqlParameter("@ActionRecordID", SqlDbType.UniqueIdentifier);
            sqlParam.Value = actionRecordId;

            SqlDataHelper.ExecuteNonQuery(
                DatabaseManager.ConnectionString,
                CommandType.Text,
                sql,
                sqlParam
                );
        }

        /// <summary>
        /// 挂起MyActivities
        /// </summary>
        /// <param name="procInstID"></param>
        public static void SuspendMyActivities(int procInstID)
        {
            string sql = "update MyActivity set Status=1,ModifiedDate=getdate() where K2ProcInstID=@K2ProcInstID and Status=0";
            SqlParameter[] parameters = {
                    new SqlParameter("@K2ProcInstID", SqlDbType.Int)
                                        };
            parameters[0].Value = procInstID;

            SqlDataHelper.ExecuteNonQuery(
                DatabaseManager.ConnectionString,
                CommandType.Text,
                sql,
                parameters
                );
        }

        /// <summary>
        /// 恢复MyActivities
        /// </summary>
        /// <param name="procInstID"></param>
        public static void ResumeMyActivities(int procInstID)
        {
            string sql = "update MyActivity set Status=0,ModifiedDate=getdate() where K2ProcInstID=@K2ProcInstID and Status=1";
            SqlParameter[] parameters = {
                    new SqlParameter("@K2ProcInstID", SqlDbType.Int)
                                        };
            parameters[0].Value = procInstID;

            SqlDataHelper.ExecuteNonQuery(
                DatabaseManager.ConnectionString,
                CommandType.Text,
                sql,
                parameters
                );
        }

        public static EmployeeTableEntitySet GetUserInfosByROLE_CD(string role_cd)
        {
            string commandText = @"select u.*
from employee as u
join UserInRole as ur on u.EmployeeNo=ur.EmployeeNo
join Role2 as r on ur.RoleID=r.RoleID
where r.Name=@role_cd and u.IsEnabled=1";
            SqlParameter[] parameters = { new SqlParameter("@role_cd", SqlDbType.NVarChar, 50) };
            parameters[0].Value = role_cd;

            DataSet ds = SqlDataHelper.ExecuteDataset(
                DatabaseManager.ConnectionString,
                CommandType.Text,
                commandText,
                parameters
                );

            EmployeeTableEntitySet set = new EmployeeTableEntitySet();
            set.Merge(ds);
            return set;
        }

        public static MyActivityTableEntity GetOneOfMyActivities(int procInstId, int ownerId, int[] statusArr)
        {
            string commandText = @"select top 1 * from MyActivity where K2ProcInstID=@ProcInstID and OwnerID=@OwnerID";

            if (statusArr != null && statusArr.Length > 0)
            {
                List<string> statusList = new List<string>();
                foreach (int status in statusArr)
                {
                    statusList.Add(status.ToString());
                }
                commandText += " and Status in (" + string.Join(",", statusList.ToArray()) + ") ";
            }

            SqlParameter[] parameters = { 
                new SqlParameter("@ProcInstID", SqlDbType.Int),
                new SqlParameter("@OwnerID", SqlDbType.Int)
            };
            parameters[0].Value = procInstId;
            parameters[1].Value = ownerId;

            DataSet ds = SqlDataHelper.ExecuteDataset(
                DatabaseManager.ConnectionString,
                CommandType.Text,
                commandText,
                parameters
                );
            if (ds == null || ds.Tables.Count == 0 || ds.Tables[0].Rows.Count == 0) return null;
            MyActivityTableEntitySet set = new MyActivityTableEntitySet();
            set.Merge(ds);
            return set[0];
        }
    }


}
